
/*
 * ALTER  the procedure for cancel the lineItem in the salesOrder 
 * and update the relative tables
 */

ALTER                  proc cancel_order_proc

/*
 * input parameters
 */

@lineItem_id int, @salesOrder_id int, @user_id varchar , @user_name varchar

AS

declare @unit_price 		float -- Added by Basha (08/29/2003) 
declare @lineItem_amount 	float
declare @sub_total 		float
declare @sales_tax 		float
declare @sales_tax_type 	varchar(20) --modified by Sarada
declare @taxable 		bit

/*
 * Insert values into Customer_Cancel_Order_LineItem from Customer_Open_Order_LineItem
 */


/* Begin : Added by Basha (08/29/2003) */

declare @cool_qty int
declare @shipped_qty int
declare @actual_qty int
--below  variable are added by Sarada (12/11/2003)
declare @tax float

select @shipped_qty = isnull(sum(cslit_qty_shipped),0) from customer_shipping_line_item_table 
			where cslit_status='SHIPPED'and LineItem_ID = @lineItem_id

select @cool_qty = cool_qty from customer_open_order_lineitem where LineItem_ID = @lineItem_id

set @actual_qty = @cool_qty - @shipped_qty


SELECT @unit_price = COOL_Unit_Cost, @taxable = COOL_Taxable From Customer_Open_order_lineItem 
			WHERE LineItem_ID = @lineItem_id

--commented below  query and added another by Sarada (12/11/2003)
/*SELECT @sub_total = S.SO_Sub_Total, @sales_tax = S.SO_Sales_Tax ,@sales_tax_type = ST.Tax 
			FROM Sales_Order S , Sales_Tax_Table ST 
			WHERE S.SalesOrder_ID = @salesOrder_id*/  --and ST.State = S.So_Sales_Tax_Type  -- commented by Sarada on 19th November 2003 to get SubTotal Value even we didn't select any Sales Tax value


SELECT @sub_total = SO_Sub_Total, @sales_tax = SO_Sales_Tax ,@sales_tax_type = So_Sales_Tax_Type
			FROM Sales_Order WHERE SalesOrder_ID = @salesOrder_id

SELECT @tax = Tax FROM Sales_Tax_Table ST WHERE STATE = @sales_tax_type
--end of additions of sarada

set @sub_total = @sub_total - (@actual_qty * @unit_price)

if(@taxable = 0)
begin
	--Modified by Sarada
	--set @sales_tax = @sales_tax - ( (@actual_qty * @unit_price) * (@sales_tax_type/100))
	set @sales_tax = @sales_tax - ( (@actual_qty * @unit_price) * (@tax/100))
end
 
/* End : Added by Basha (08/29/2003) */


/* Moved CCOL_Qty and CCOL_Amount from INSERT stament to UPDATE statement by Basha (08/29/2003) */

INSERT INTO Customer_Cancel_Order_LineItem (LineItem_ID, SalesOrder_ID, CCOL_Line_Item_No,CCOL_PO_Line_Item_No,
	Product_ID, CCOL_Unit_Cost, CCOL_Requested_Date, 
	CCOL_Booking_Date,CCOL_Sugg_Schedule_Date_With_Substitutables,
	CCOL_Sugg_Schedule_Date_Without_Substitutables,CCOL_Schedule_Date, CCOL_Revised_Date,CCOL_Taxable, CCOL_CUSTOM_PRICE_FIELD1, CCOL_CUSTOM_TEXT_FIELD1)
	 (SELECT LineItem_ID, SalesOrder_ID, COOL_Line_Item_No,COOL_PO_Line_Item_No,
		Product_ID, COOL_Unit_Cost, COOL_Requested_Date, 
		COOL_Booking_Date,COOL_Sugg_Schedule_Date_With_Substitutables,
		COOL_Sugg_Schedule_Date_Without_Substitutables,COOL_Schedule_Date,
		COOL_Revised_Date,COOL_Taxable, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 FROM Customer_Open_Order_LineItem 
		WHERE LineItem_ID = @lineItem_id)



/*
 * Update the Customer_Cancel_Order_LineItem 
 * for CancelById , CancelByName
 */

UPDATE Customer_Cancel_Order_LineItem 
	SET CCOL_Cancel_By_ID = @user_id, CCOL_Cancel_By_Name = @user_name,
	TimeStamp = CURRENT_TIMESTAMP,
	CCOL_Qty = @actual_qty , CCOL_Amount = @actual_qty * @unit_price -- Added by Basha (08/29/200)
	WHERE LineItem_ID = @lineItem_Id

/*
 * Delete the LineItem from the Customer_Open_Order_LineItem
 */

/* Begin: Added by Basha (08/29/2003) */

if(@shipped_qty > 0)
begin
	UPDATE Customer_Open_Order_LineItem SET COOL_Qty = @shipped_qty , COOL_Amount = @shipped_qty * @unit_price ,
		COOL_Status = 'SHIPPED' , COOL_Is_Cancel = 1 WHERE LineItem_Id = @lineItem_id
end
else
begin
	DELETE FROM Customer_Open_Order_LineItem WHERE LineItem_Id = @lineItem_id
end

/* End: Added by Basha (08/29/2003) */


/* Commented by Basha (08/29/2003)
SELECT @lineItem_amount = CCOL_Amount, @taxable = CCOL_Taxable From Customer_Cancel_order_lineItem WHERE LineItem_ID = @lineItem_id
SELECT @sub_total = S.SO_Sub_Total, @sales_tax = S.SO_Sales_Tax ,@sales_tax_type = ST.Tax FROM Sales_Order S ,
Sales_Tax_Table ST WHERE S.SalesOrder_ID = @salesOrder_id and ST.State = S.So_Sales_Tax_Type 

set @sub_total = @sub_total - @lineItem_amount

if(@taxable = 0)
begin
	set @sales_tax = @sales_tax - (@lineItem_amount * (@sales_tax_type/100))
end
*/


/*
 * Update the Sales_Order Table as it's lineItem is cancelled
 */

UPDATE Sales_Order SET SO_Is_Cancel = 0, SO_Sub_Total = @sub_total,
	SO_Sales_Tax = @sales_tax, TimeStamp = CURRENT_TIMESTAMP
	WHERE SalesOrder_ID = @salesOrder_id

/* Added by Sarada (12/11/2003) to update the SubTotal,SalesTax vlues in Change_Sales_order Table also*/
UPDATE Change_Sales_Order SET CSO_Sub_Total = @sub_total,
	CSO_Sales_Tax = @sales_tax, TimeStamp = CURRENT_TIMESTAMP
	WHERE SalesOrder_ID = @salesOrder_id and ChangeNo =(SELECT max(ChangeNo) from Change_Sales_Order WHERE SalesOrder_ID = @salesOrder_id)

/* 
 * Delete the holds on this order
 */

DELETE LineItem_Hold_Table WHERE LineItem_ID = @lineItem_id











